﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sinacor.Infra.Core.Licensing.Data
{
	public class Aliases : ResourceAccess
	{
		public int SelecionarProximoIdAlias()
		{
			int retorno = 0;

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				string sql = " SELECT NVL(MAX(COD_ALIA),0)+1 FROM TFWDALIA ";
				command.CommandText = sql.ToString();
				retorno = Convert.ToInt32(this.ExecuteScalar(command));
			}

			return retorno;
		}

		public Sinacor.Infra.Core.Licensing.Alias SelecionarAlias(Sinacor.Infra.Core.Licensing.Alias library)
		{
			Sinacor.Infra.Core.Licensing.Alias response = null;

			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@" SELECT COD_ALIA
                                        ,NOME_ALIA
                                  FROM TFWDALIA
                                  WHERE (COD_ALIA = :COD_ALIA) 
                                  AND   (NOME_ALIA = :NOME_ALIA) ");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);

				command.CommandText = sql.ToString();

				using (IDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						response = new Sinacor.Infra.Core.Licensing.Alias
						{
							ID = reader.GetValue<int>("COD_ALIA"),
							Nome = reader.GetValue<string>("NOME_ALIA")
						};
					}
				}
			}

			return response;
		}

        public Sinacor.Infra.Core.Licensing.Alias SelecionarCodAlias(Sinacor.Infra.Core.Licensing.Alias library)
        {
            Sinacor.Infra.Core.Licensing.Alias response = null;

            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                sql.AppendLine(@" SELECT COD_ALIA
                                        ,NOME_ALIA
                                  FROM TFWDALIA
                                  WHERE (NOME_ALIA = :NOME_ALIA) ");

                this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);

                command.CommandText = sql.ToString();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        response = new Sinacor.Infra.Core.Licensing.Alias
                        {
                            ID = reader.GetValue<int>("COD_ALIA"),
                            Nome = reader.GetValue<string>("NOME_ALIA")
                        };
                    }
                }
            }

            return response;
        }

		public bool VerificarAliasExiste(Sinacor.Infra.Core.Licensing.Alias library)
		{
			bool response;
			int Total = 0;
			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@" SELECT COUNT(*) AS TOTAL 
                                  FROM TFWDALIA WHERE NOME_ALIA = :NOME_ALIA");

				this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);

				command.CommandText = sql.ToString();
				using (IDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						Total = reader.GetValue<int>("TOTAL");
					}
				}
				response = Total > 0 ? true : false;
			}

			return response;
		}

		public void Incluir(Sinacor.Infra.Core.Licensing.Alias library)
		{
			StringBuilder sql = new StringBuilder();
			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@" INSERT INTO TFWDALIA (COD_ALIA, NOME_ALIA)
                                  VALUES (:COD_ALIA, :NOME_ALIA)");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);

				command.CommandText = sql.ToString();
				ExecuteNonQuery(command);
			}
		}

		public void IncluirNovaCompanhia(Sinacor.Infra.Core.Licensing.Alias library, Sinacor.Infra.Core.Licensing.Company company)
		{
			StringBuilder sql = new StringBuilder();
			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@" INSERT INTO TFWDALIA_CORR (COD_ALIA, COD_CORR)
                                  VALUES (:COD_ALIA, :COD_CORR)");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				this.AddInParameter(command, "COD_CORR", DbType.String, company.CompanyId);

				command.CommandText = sql.ToString();
				ExecuteNonQuery(command);
			}
		}

		public void Alterar(Sinacor.Infra.Core.Licensing.Alias library)
		{
			StringBuilder sql = new StringBuilder();
			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@"UPDATE TFWDALIA SET NOME_ALIA = :NOME_ALIA WHERE COD_ALIA = :COD_ALIA");

				this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);
				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);

				command.CommandText = sql.ToString();
				ExecuteNonQuery(command);
			}
		}

		public void Excluir(Sinacor.Infra.Core.Licensing.Alias library)
		{
			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				string sql = " DELETE FROM TFWDALIA WHERE COD_ALIA = :COD_ALIA ";
				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);

				command.CommandText = sql.ToString();
				this.ExecuteNonQuery(command);
			}
		}

		public void AtualizarCompaniasVinculadas(Sinacor.Infra.Core.Licensing.Alias library)
		{
			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				string sql = "DELETE FROM TFWDALIA_CORR WHERE COD_ALIA = :COD_ALIA";
				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				command.CommandText = sql.ToString();
				this.ExecuteNonQuery(command);
			}

			if (library.AttachedCompanies != null && library.AttachedCompanies.Count > 0)
			{
				foreach (Company company in library.AttachedCompanies)
				{
					using (IDbCommand command = this.GetSqlStringCommand(" "))
					{
						StringBuilder sqlInsert = new StringBuilder();

						sqlInsert.AppendLine(@"INSERT INTO TFWDALIA_CORR (COD_ALIA, COD_CORR) VALUES (:COD_ALIA, :COD_CORR)");
						this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
						this.AddInParameter(command, "COD_CORR", DbType.Int32, company.CompanyId);

						command.CommandText = sqlInsert.ToString();
						this.ExecuteNonQuery(command);
					}
				}
			}
		}

		public List<Sinacor.Infra.Core.Licensing.Alias> Selecionar(Sinacor.Infra.Core.Licensing.Alias library)
		{
			List<Sinacor.Infra.Core.Licensing.Alias> response = null;

			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@" SELECT COD_ALIA
                                        ,NOME_ALIA
                                  FROM TFWDALIA
                                  WHERE (COD_ALIA = :COD_ALIA OR :COD_ALIA IS NULL) 
                                  AND   (NOME_ALIA LIKE :NOME_ALIA OR :NOME_ALIA IS NULL) 
                                  ORDER BY NOME_ALIA");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				this.AddInParameter(command, "NOME_ALIA", DbType.String, library.Nome);

				command.CommandText = sql.ToString();
				response = this.FindAll<Sinacor.Infra.Core.Licensing.Alias>(command,
					(reader => new Sinacor.Infra.Core.Licensing.Alias
					{
						ID = reader.GetValue<int>("COD_ALIA"),
						Nome = reader.GetValue<string>("NOME_ALIA")
					}
					)
				);
			}

			return response;
		}

		public List<Company> SelecionarCompaniasVinculadas(Sinacor.Infra.Core.Licensing.Alias library)
		{
			List<Company> companies = new List<Company>();

			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@"SELECT T1.COD_CORR
										,T1.NOME_CORR
										,T1.COD_CNPJ_CORR
										,T1.COD_CORR_SINACOR
									FROM 
										TFWDCORR T1, TFWDALIA_CORR T2
									WHERE 
										T2.COD_ALIA = :COD_ALIA
										AND T1.COD_CORR = T2.COD_CORR
									ORDER BY 
										T1.NOME_CORR
								");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);

				command.CommandText = sql.ToString();
				companies = this.FindAll<Sinacor.Infra.Core.Licensing.Company>(command,
					(reader => new Sinacor.Infra.Core.Licensing.Company
					{
						CompanyId = reader.GetValue<int>("COD_CORR"),
						Name = reader.GetValue<string>("NOME_CORR"),
						LegalRegistrationId = reader.GetValue<string>("COD_CNPJ_CORR"),
						SinacorCompanyId = reader.GetValue<int?>("COD_CORR_SINACOR")
					}
					)
				);
			}

			return companies;
		}

		public List<Company> SelecionarCompaniasNaoVinculadas(Sinacor.Infra.Core.Licensing.Alias library)
		{
			List<Company> companies = new List<Company>();

			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				if (library != null)
				{
					sql.AppendLine(@"SELECT T1.COD_CORR
										,T1.NOME_CORR
										,T1.COD_CNPJ_CORR
										,T1.COD_CORR_SINACOR
									FROM 
										TFWDCORR T1
									WHERE 
										T1.COD_CORR NOT IN (SELECT COD_CORR FROM TFWDALIA_CORR WHERE COD_ALIA = :COD_ALIA)
									ORDER BY 
										T1.NOME_CORR
								");

					this.AddInParameter(command, "COD_ALIA", DbType.Int32, library.ID);
				}
				else
				{
					sql.AppendLine(@"SELECT T1.COD_CORR
										,T1.NOME_CORR
										,T1.COD_CNPJ_CORR
										,T1.COD_CORR_SINACOR
									FROM 
										TFWDCORR T1
									ORDER BY 
										T1.NOME_CORR");
				}

				command.CommandText = sql.ToString();
				companies = this.FindAll<Sinacor.Infra.Core.Licensing.Company>(command,
					(reader => new Sinacor.Infra.Core.Licensing.Company
					{
						CompanyId = reader.GetValue<int>("COD_CORR"),
						Name = reader.GetValue<string>("NOME_CORR"),
						LegalRegistrationId = reader.GetValue<string>("COD_CNPJ_CORR"),
						SinacorCompanyId = reader.GetValue<int?>("COD_CORR_SINACOR")
					}
					)
				);
			}

			return companies;
		}
	}
}